using System;
using System.Data.OleDb;
using System.IO;
using System.Data;
using System.Data.OracleClient;

namespace CustomOC
{
    /// <summary>
    /// This class contains some helper fucntions tpo be used in OCSync class
    /// </summary>
    public class Helper
    {
        private const string strConnection = "Data Source=GW_IDMP_PROD;User Id=hadf;Password=hadf;Integrated Security=no;";//"Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=ABInbevDB;Integrated Security=SSPI;";
        private string strLogFilePath = "C:\\UltimusLog\\CustomOCLog" + DateTime.Now.ToShortDateString() + ".log";
        private StreamWriter logWriter;

        /// <summary>
        /// constructor 
        /// </summary>
        public Helper()
        {
        }

        /// <summary>
        /// This method executes the specifed query and returns the result in a record set
        /// </summary>
        /// <param name="strQuery">SQL query to be executed as a string</param>
        /// <param name="strError">contains the error description if method fails</param>
        /// <param name="dbConnection">An object of OleDbConnection.</param>
        /// <returns>An object of type OleDbDataReader containing record set returned by the SQL query</returns>
        public OracleDataReader RunQuery(string strQuery, out string strError, out OracleConnection dbConnection)
        {
            OracleDataReader dbReader = null;
            strError = "";
          
            try
            {
                dbConnection = new OracleConnection(strConnection);
                dbConnection.Open();
                OracleCommand dbCmd = new OracleCommand(strQuery, dbConnection);
                dbCmd.CommandTimeout = 1000;

                //Execute the query
                dbReader = dbCmd.ExecuteReader();

            }
            catch (Exception Ex)
            {
                strError = Ex.Message;
                dbConnection = null;
                return null;
            }

            //return reader
            strError = "";
            return dbReader;
        }   
        /// <summary>
        /// This method executes the specifed query and returns the result in a record set
        /// </summary>
        /// <param name="strQuery">SQL query to be executed as a string</param>
        /// <param name="strError">contains the error description if method fails</param>
        /// <param name="dbConnection">An object of OleDbConnection.</param>
        /// <returns>An object of type OleDbDataReader containing record set returned by the SQL query</returns>
        public OracleDataReader RunQueryRef(string strQuery, out string strError, ref OracleConnection dbConnection)
        {
            OracleDataReader dbReader = null;
            strError = "";
            //   dbConnection = null;
            try
            {
                if (dbConnection == null)
                    dbConnection = new OracleConnection(strConnection);
                if (dbConnection.State == ConnectionState.Closed)
                    dbConnection.Open();
                OracleCommand dbCmd = new OracleCommand(strQuery, dbConnection);
                dbCmd.CommandTimeout = 1000;

                //Execute the query
                dbReader = dbCmd.ExecuteReader();

            }
            catch (Exception Ex)
            {
                strError = Ex.Message;
                dbConnection = null;
                return null;
            }

            //return reader
            strError = "";
            return dbReader;
        }
        /// <summary>
        /// Executes the select query and returns the total number of rows returned
        /// </summary>
        /// <param name="strQuery">SQL query to be executed as a string</param>
        /// <returns>Total number of rows returned by the SQL query</returns>
        public int GetTotalRows(string strQuery)
        {
            int nRows = 0;

            try
            {
                OracleConnection dbConnection = new OracleConnection(strConnection);
                dbConnection.Open();
                OracleCommand dbCmd = new OracleCommand(strQuery, dbConnection);
                dbCmd.CommandTimeout = 1000;

                //Execute the query and get the rows
                OracleDataReader dbReader;
                dbReader = dbCmd.ExecuteReader();

                while (dbReader.Read())
                    nRows++;

                dbReader.Close();

                dbConnection.Close();
            }
            catch (Exception Ex)
            {
                AppendTextToLog("GetTotalRows failed with exception : " + Ex.Message);
                AppendTextToLog("GetTotalRows failed with exception sql: " + strQuery);
                return -1;
            }

            //if (nRows == 0)
            //{
            //AppendTextToLog("sql: " + strQuery);
            //}

            return nRows;
        }

        /// <summary>
        /// Appends a text line to the log file
        /// </summary>
        /// <param name="strText">Text to be appended</param>
        public void AppendTextToLog(string strText)
        {
            logWriter = File.AppendText(strLogFilePath);
            logWriter.Write(DateTime.Now.ToString() + ": ");
            logWriter.WriteLine(strText);
            logWriter.Close();
            logWriter.Dispose();
        }

    }
}
